package com.api.gbhg.liuwenbo.dao;

import com.google.common.base.Joiner;
import weaver.conn.RecordSet;
import weaver.conn.RecordSetTrans;

import java.time.LocalDate;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class VerificationDBUtil {

    private static final String UF_DFCZBKXM = "uf_dfczbkxm";
    private static final String UF_FZYCZBKJCSJ = "uf_fzyczbkjcsj";
    private static final String UF_FZYCZBKSR = "uf_fzysr";
    private static final String UF_FZYCZBKSRXM = "uf_fzysrxm";
    private static final String UF_ZYCZBKSR_XJ = "uf_zyczbksr_xj";
    private static final String UF_ZYCZBKSR_KM = "uf_zyczbksr_km";


    /**
     * 根据所在分类级单位跟年份获取非中央id
     * @param dw
     * @param flId
     * @return
     */
    public static String getFzyIdByFlid(Integer dw,String flId){
        RecordSet rs = new RecordSet();
        String sql = " select id from uf_fzysr where dw = "+dw+" and nf = "+ LocalDate.now().getYear()+" and szfl = "+flId+" ";
        rs.executeQuery(sql);
        rs.next();
        return rs.getString("id");
    }

    /**
     * 拼接修改kmsql
     * @param zydnzckm
     * @param zysnzckm
     * @param zyConditionIdKm
     * @return
     */
    public static String setupKm(StringBuffer zydnzckm,StringBuffer zysnzckm,StringBuffer zydnzckm2,
                                 StringBuffer zysnzckm2,StringBuffer  zydnzckm3,List<String> zyConditionIdKm,StringBuffer zysnjzhpjekm,StringBuffer zysnjzhpsyjekm){
        StringBuffer upsql = new StringBuffer();
        if (!zydnzckm.toString().isEmpty()){
            if (upsql.toString().isEmpty())
                upsql.append(" update "+UF_ZYCZBKSR_KM+" set ");

            upsql.append(" yjsrsy = (case "+zydnzckm.toString()+" else yjsr end) ");
            upsql.append(", yhpje = (case "+zydnzckm2.toString()+" else yhpje end) ");
            upsql.append(", hpsyje = (case "+zydnzckm3.toString()+" else hpsyje end) ");
        }
        if (!zysnzckm.toString().isEmpty()){
            if (upsql.toString().isEmpty())
                upsql.append(" update "+UF_ZYCZBKSR_KM+" set ");

            if (zydnzckm.toString().isEmpty()){
                upsql.append("yqndyjjzyyje = (case "+zysnzckm2.toString()+" else yqndyjjzyyje end) ");
                upsql.append(",yqndyjjzsyje = (case "+zysnzckm.toString()+" else yqndyjjzsyje end) ");
                upsql.append(",snjzhpje = (case "+zysnjzhpjekm.toString()+" end )  ");
                upsql.append(",snjzhpsyje = (case "+zysnjzhpsyjekm.toString()+" end )  ");
            }else{
                upsql.append(", yqndyjjzyyje = (case "+zysnzckm2.toString()+" else yqndyjjzyyje end) ");
                upsql.append(", yqndyjjzsyje = (case "+zysnzckm.toString()+" else yqndyjjzsyje end) ");
                upsql.append(", snjzhpje = (case "+zysnjzhpjekm.toString()+" end )  ");
                upsql.append(", snjzhpsyje = (case "+zysnjzhpsyjekm.toString()+" end )  ");
            }
        }
        if (!upsql.toString().isEmpty()){
            return upsql.append(" where id in ("+Joiner.on(",").join((Iterable<?>) zyConditionIdKm)+") ").toString();
        }else{
            return upsql.toString();
        }
    }


    /**
     * 拼接修改下级sql
     * @param zydnzcxj
     * @param zysnzcxj
     * @param zydnzcxj2
     * @param zysnzcxj2
     * @param zyConditionIdXj
     * @param zysnjzhpjexj
     * @param zysnjzhpsyjexj
     * @return
     */
    public static String setupXj(StringBuffer zydnzcxj,StringBuffer zysnzcxj,
                                 StringBuffer zydnzcxj2,StringBuffer zysnzcxj2,List<String> zyConditionIdXj,StringBuffer zysnjzhpjexj,StringBuffer zysnjzhpsyjexj){
        StringBuffer upsql = new StringBuffer();
        if (!zydnzcxj.toString().isEmpty()){
            if (upsql.toString().isEmpty())
                upsql.append(" update "+UF_ZYCZBKSR_XJ+" set ");

            //upsql.append(" yjsr = (case "+zydnzcxj.toString()+" else yjsr end) ");
            upsql.append("yhpje = (case "+zydnzcxj2.toString()+" else yhpje end) ");
        }
        if (!zysnzcxj.toString().isEmpty()){
            if (upsql.toString().isEmpty())
                upsql.append(" update "+UF_ZYCZBKSR_XJ+" set ");

            if (zydnzcxj.toString().isEmpty()){
                upsql.append(" yqndyjjzsyje = (case "+zysnzcxj.toString()+" else yqndyjjzsyje end) ");
                upsql.append(",yqndyjjzyyje = (case "+zysnzcxj2.toString()+" else yqndyjjzyyje end) ");
                upsql.append(",snjzhpje = (case "+zysnjzhpjexj.toString()+" end )  ");
                upsql.append(",snjzhpsyje = (case "+zysnjzhpsyjexj.toString()+" end )  ");
            }else{
                upsql.append(", yqndyjjzsyje = (case "+zysnzcxj.toString()+" else yqndyjjzsyje end) ");
                upsql.append(", yqndyjjzyyje = (case "+zysnzcxj2.toString()+" else yqndyjjzyyje end) ");
                upsql.append(",snjzhpje = (case "+zysnjzhpjexj.toString()+" end )  ");
                upsql.append(",snjzhpsyje = (case "+zysnjzhpsyjexj.toString()+" end )  ");
            }
        }
        if (!upsql.toString().isEmpty()){
            return upsql.append(" where id in ("+Joiner.on(",").join((Iterable<?>) zyConditionIdXj)+") ").toString();
        }else{
            return upsql.toString();
        }
    }

    /**
     * 拼接修改非中央sql
     * @param fzydnzc
     * @param fzysnzc
     * @param fzydnzc2
     * @param fzysnzc2
     * @param fzydnzc3
     * @param fzyConditionId
     * @param fzysnjzhpje
     * @param fzysnjzhpsyje
     * @return
     */
    public static String setupFzy(StringBuffer fzydnzc,StringBuffer fzysnzc,StringBuffer fzydnzc2,
                                  StringBuffer fzysnzc2,StringBuffer fzydnzc3,List<String> fzyConditionId,StringBuffer fzysnjzhpje,StringBuffer fzysnjzhpsyje){
        StringBuffer upsql = new StringBuffer();
        if (!fzydnzc.toString().isEmpty()){
            if (upsql.toString().isEmpty())
                upsql.append(" update "+UF_FZYCZBKSR+" set ");

            upsql.append(" yjsrsy = (case "+fzydnzc.toString()+" else yjsrsy end) ");
            upsql.append(", yhpje = (case "+fzydnzc2.toString()+" else yhpje end) ");
            upsql.append(", hpsyje = (case "+fzydnzc3.toString()+" else hpsyje end) ");
        }
        if (!fzysnzc.toString().isEmpty()){
            if (upsql.toString().isEmpty())
                upsql.append(" update "+UF_FZYCZBKSR+" set ");

            if (fzydnzc.toString().isEmpty()){
                /*upsql.append(" jzsyje = (case "+fzysnzc.toString()+" else jzsyje end) ");
                upsql.append(",jzyyje = (case "+fzysnzc2.toString()+" else jzyyje end) ");*/
                upsql.append("yqndyjjzyyje = (case "+fzysnzc2.toString()+" else yqndyjjzyyje end) ");
                upsql.append(",yqndyjjzsyje = (case "+fzysnzc.toString()+" else yqndyjjzsyje end) ");
                upsql.append(",snjzhpje = (case "+fzysnjzhpje.toString()+" end )  ");
                upsql.append(",snjzhpsyje = (case "+fzysnjzhpsyje.toString()+" end )  ");
            }else{
                upsql.append(", yqndyjjzyyje = (case "+fzysnzc2.toString()+" else yqndyjjzyyje end) ");
                upsql.append(", yqndyjjzsyje = (case "+fzysnzc.toString()+" else yqndyjjzsyje end) ");
                upsql.append(",snjzhpje = (case "+fzysnjzhpje.toString()+" end )  ");
                upsql.append(",snjzhpsyje = (case "+fzysnjzhpsyje.toString()+" end )  ");
            }
        }
        if (!upsql.toString().isEmpty()){
            return upsql.append(" where id in ("+Joiner.on(",").join((Iterable<?>) fzyConditionId)+") ").toString();
        }else{
            return upsql.toString();
        }
    }

    /**
     * 拼接修改非中央sql
     * @param fzydnxmzc
     * @param fzysnxmzc
     * @param fzydnxmzc2
     * @param fzysnxmzc2
     * @param fzydnxmzc3
     * @param fzyxmConditionId
     * @param fzysnjzhpjexm
     * @param fzysnjzhpsyjexm
     * @return
     */
    public static String setupFzyxm(StringBuffer fzydnxmzc,StringBuffer fzysnxmzc,StringBuffer fzydnxmzc2,
                                    StringBuffer fzysnxmzc2,StringBuffer fzydnxmzc3,List<String> fzyxmConditionId,StringBuffer fzysnjzhpjexm,StringBuffer fzysnjzhpsyjexm){
        StringBuffer upsql = new StringBuffer();
        if (!fzydnxmzc.toString().isEmpty()){
            if (upsql.toString().isEmpty())
                upsql.append(" update "+UF_FZYCZBKSRXM+" set ");

            upsql.append(" yjsrsy = (case "+fzydnxmzc.toString()+" else yjsrsy end) ");
            upsql.append(", yhpje = (case "+fzydnxmzc2.toString()+" else yhpje end) ");
            upsql.append(", hpsyje = (case "+fzydnxmzc3.toString()+" else hpsyje end) ");
        }
        if (!fzysnxmzc.toString().isEmpty()){
            if (upsql.toString().isEmpty())
                upsql.append(" update "+UF_FZYCZBKSRXM+" set ");

            if (fzydnxmzc.toString().isEmpty()){
                /*upsql.append(" jzsyje = (case "+fzysnzc.toString()+" else jzsyje end) ");
                upsql.append(",jzyyje = (case "+fzysnzc2.toString()+" else jzyyje end) ");*/
                upsql.append("yqndyjjzyyje = (case "+fzysnxmzc2.toString()+" else yqndyjjzyyje end) ");
                upsql.append(",yqndyjjzsyje = (case "+fzysnxmzc.toString()+" else yqndyjjzsyje end) ");
                upsql.append(",snjzhpje = (case "+fzysnjzhpjexm.toString()+" end )  ");
                upsql.append(",snjzhpsyje = (case "+fzysnjzhpsyjexm.toString()+" end )  ");
            }else{
                upsql.append(", yqndyjjzyyje = (case "+fzysnxmzc2.toString()+" else yqndyjjzyyje end) ");
                upsql.append(", yqndyjjzsyje = (case "+fzysnxmzc.toString()+" else yqndyjjzsyje end) ");
                upsql.append(",snjzhpje = (case "+fzysnjzhpjexm.toString()+" end )  ");
                upsql.append(",snjzhpsyje = (case "+fzysnjzhpsyjexm.toString()+" end )  ");
            }
        }
        if (!upsql.toString().isEmpty()){
            return upsql.append(" where id in ("+Joiner.on(",").join((Iterable<?>) fzyxmConditionId)+") ").toString();
        }else{
            return upsql.toString();
        }
    }

    /**
     * 扣减下级金额，同时扣减所有上级
     * @param ids
     * @param je
     * @param jeStr1
     * @param jeStr2
     * @param ix
     * @return
     * @throws Exception
     */
    public static String joinXjSql(String ids,String je,String jeStr1,String jeStr2,Integer ix) throws Exception {
//        String sql = "update uf_zyczbksr_xj set "+jeStr1+" = (case when id in ("+ids+") then "+jeStr1+" - "+je+" else "+jeStr1+" end) where id in ("+ids+") ";
        String sql ;
        if (ix==0){
            sql = "update uf_zyczbksr_xj set "+jeStr1+" = "+jeStr1+" - "+je +
                    " ,snjzhpje = isnull(snjzhpje,0)+"+je+",snjzhpsyje = isnull(snjzhpsyje,0)+"+je+" where id in("+ids+")  ";
        }else{
            sql = "update uf_zyczbksr_xj set "+jeStr1+" = "+jeStr1+" - "+je+" ,yhpsy = yhpsy + "+je+"   where id in("+ids+")  ";
        }
        return sql;
    }

    /**
     * 扣减科目金额
     * @param id
     * @param jeStr1
     * @param je
     * @return
     */
    public static String joinKmsql(String id,String jeStr1,String jeStr2,String je,Integer ix){
        RecordSet rs = new RecordSet();
        String sql = " select szgnkm from uf_zyczbksr_xj where id = "+id+" ";
        rs.executeQuery(sql);
        rs.next();
        String sqlKm ;
        if (ix == 0){
            sqlKm = " update uf_zyczbksr_km set  "+jeStr1+" = "+jeStr1+" - "+je+" , "+jeStr2+" = "+jeStr2+" + "+je+"," +
                    " snjzhpje = isnull(snjzhpje,0)+"+je+",snjzhpsyje = isnull(snjzhpsyje,0)+"+je+" where id = "+rs.getString("szgnkm")+"  ";
        }else{
            sqlKm = " update uf_zyczbksr_km set  "+jeStr1+" = "+jeStr1+" - "+je+" , "+jeStr2+" = "+jeStr2+" + "+je+", hpsyje = hpsyje + "+je+" where id = "+rs.getString("szgnkm")+"  ";
        }

        return sqlKm;
    }



    public static String setdfSql(StringBuilder dfczDn, StringBuilder dfczSn, StringBuilder dfdnzc, StringBuilder dfsnzc, List<String> dfIdList){
        StringBuffer dfSql = new StringBuffer();
        if (!dfczDn.toString().isEmpty()){
            if (dfSql.toString().isEmpty() ){
                dfSql.append(" update "+UF_DFCZBKXM+" set ");
            }
            dfSql.append(" yjsrsy = (case "+dfczDn.toString() +"else yjsrsy end)  ");
        }
        if(!dfczSn.toString().isEmpty()){
            if (dfSql.toString().isEmpty() ){
                dfSql.append(" update "+UF_DFCZBKXM+" set ");
            }

            if (dfczDn.toString().isEmpty())
                dfSql.append(" jzsyje = (case "+dfczSn.toString() +"else jzsyje end)  ");
            else
                dfSql.append(", jzsyje = (case "+dfczSn.toString() +"else jzsyje end)  ");
        }

        if (!dfSql.toString().isEmpty()){
            if (!dfdnzc.toString().isEmpty()&&dfsnzc.toString().isEmpty()){ //syje1=地方财政当年 syje2=地方财政上年
                dfSql.append(" , yhpje = (case "+dfdnzc.toString()+" else yhpje end) ");
            }else if(dfdnzc.toString().isEmpty()&&!dfsnzc.toString().isEmpty()){
                dfSql.append(" , jzyyje = (case "+dfsnzc.toString()+" else jzyyje end) ");
            }else{
                dfSql.append(" , yhpje = (case "+dfdnzc.toString()+" else yhpje end) , jzyyje = (case "+dfsnzc.toString()+" else jzyyje end) ");
            }
            dfSql.append(" where id in ("+ Joiner.on(",").join((Iterable<?>) dfIdList)+")");
//            s1 = rst.executeUpdate(dfSql.toString());
        }
        return dfSql.toString();
    }

    public static String setfzySql(StringBuilder fzyDn,StringBuilder fzySn,StringBuilder fzydnzc,StringBuilder fzysnzc,List<String> fzyIdList){
        StringBuffer fzySql = new StringBuffer();
        if (!fzyDn.toString().isEmpty()){
            if (fzySql.toString().isEmpty() ){
                fzySql.append(" update "+UF_FZYCZBKJCSJ+" set ");
            }
            fzySql.append(" yjsrsy = (case "+fzyDn.toString() +"else yjsrsy end)");
        }
        if(!fzySn.toString().isEmpty()){
            if (fzySql.toString().isEmpty() ){
                fzySql.append(" update "+UF_FZYCZBKJCSJ+" set ");
            }
            if (fzyDn.toString().isEmpty())
                fzySql.append(" jzsyje = (case "+fzySn.toString() +"else jzsyje end) ");
            else
                fzySql.append(", jzsyje = (case "+fzySn.toString() +"else jzsyje end) ");
        }

        if (!fzySql.toString().isEmpty()){
            if (!fzydnzc.toString().isEmpty()&&fzysnzc.toString().isEmpty()){ //syje3=非中央当年 syje4=非中央上年
                fzySql.append(" , yhpje = (case "+fzydnzc.toString()+" else yhpje end) ");
            }else if(fzydnzc.toString().isEmpty()&&!fzysnzc.toString().isEmpty()){
                fzySql.append(" , jzyyje = (case "+fzysnzc.toString()+" else jzyyje end) ");
            }else{
                fzySql.append(", yhpje = (case "+fzydnzc.toString()+" else yhpje end) , jzyyje = (case "+fzysnzc.toString()+" else jzyyje end) ");
            }
            fzySql.append(" where srly in ("+Joiner.on(",").join((Iterable<?>) fzyIdList)+")");
//            s2 = rst.executeUpdate(fzySql.toString());
        }
        return fzySql.toString();
    }


    public static String setkmSql(StringBuilder kmDn,StringBuilder kmSn,StringBuilder kmdnzc,StringBuilder kmsnzc,List<String> kmIdList){
        StringBuffer kmSql = new StringBuffer();
        if (!kmDn.toString().isEmpty()){
            if (kmSql.toString().isEmpty() ){
                kmSql.append(" update "+UF_ZYCZBKSR_KM+" set ");
            }
            kmSql.append(" yjsrsy = (case "+kmDn.toString() +"else yjsrsy end)");
        }
        if(!kmSn.toString().isEmpty()){
            if (kmSql.toString().isEmpty() ){
                kmSql.append(" update "+UF_ZYCZBKSR_KM+" set ");
            }
            if (kmDn.toString().isEmpty())
                kmSql.append(" jzsyje = (case "+kmSn.toString() +"else jzsyje end) ");
            else
                kmSql.append(", jzsyje = (case "+kmSn.toString() +"else jzsyje end) ");
        }

        if (!kmSql.toString().isEmpty()){
            if (!kmdnzc.toString().isEmpty()&&kmsnzc.toString().isEmpty()){ //kmdnzc=当年支出 kmsnzc=上年支出
                kmSql.append(" , yhpje = (case "+kmdnzc.toString()+" else yhpje end) ");
            }else if(kmdnzc.toString().isEmpty()&&!kmsnzc.toString().isEmpty()){
                kmSql.append(" , jzyyje = (case "+kmsnzc.toString()+" else jzyyje end) ");
            }else{
                kmSql.append(", yhpje = (case "+kmdnzc.toString()+" else yhpje end) , jzyyje = (case "+kmsnzc.toString()+" else jzyyje end) ");
            }
            kmSql.append(" where id in ("+Joiner.on(",").join((Iterable<?>) kmIdList)+")");
//            s2 = rst.executeUpdate(fzySql.toString());
        }
        return kmSql.toString();
    }

    public static String setxjSql(StringBuilder xjDn,StringBuilder xjSn,StringBuilder xjdnzc,StringBuilder xjsnzc,List<String> xjIdList){
        StringBuffer xjSql = new StringBuffer();
        if (!xjDn.toString().isEmpty()){
            if (xjSql.toString().isEmpty() ){
                xjSql.append(" update "+UF_ZYCZBKSR_XJ+" set ");
            }
            xjSql.append(" yjsrsy = (case "+xjDn.toString() +"else yjsrsy end)");
        }
        if(!xjSn.toString().isEmpty()){
            if (xjSql.toString().isEmpty() ){
                xjSql.append(" update "+UF_ZYCZBKSR_XJ+" set ");
            }
            if (xjDn.toString().isEmpty())
                xjSql.append(" jzsyje = (case "+xjSn.toString() +"else jzsyje end) ");
            else
                xjSql.append(", jzsyje = (case "+xjSn.toString() +"else jzsyje end) ");
        }

        if (!xjSql.toString().isEmpty()){
            if (!xjdnzc.toString().isEmpty()&&xjsnzc.toString().isEmpty()){ //xjdnzc=当年支出 xjsnzc=上年支出
                xjSql.append(" , yhpje = (case "+xjdnzc.toString()+" else yhpje end) ");
            }else if(xjdnzc.toString().isEmpty()&&!xjsnzc.toString().isEmpty()){
                xjSql.append(" , jzyyje = (case "+xjsnzc.toString()+" else jzyyje end) ");
            }else{
                xjSql.append(", yhpje = (case "+xjdnzc.toString()+" else yhpje end) , jzyyje = (case "+xjsnzc.toString()+" else jzyyje end) ");
            }
            xjSql.append(" where id in ("+Joiner.on(",").join((Iterable<?>) xjIdList)+")");
//            s2 = rst.executeUpdate(fzySql.toString());
        }
        return xjSql.toString();
    }

    /**
     * 根据下级id获取一级id
     * @param id
     * @return
     */
    public static String getKmIdByXjId(String id){
        RecordSet rs = new RecordSet();
        String sql = " select szgnkm from "+UF_ZYCZBKSR_XJ+" where id = "+id+" ";
        rs.executeQuery(sql);
        rs.next();
        return rs.getString("szgnkm");
    }

    /**
     * 根据非中央收入项目id获取所有非中央收入表id
     * @param fzyxmConditionId
     * @return
     */
    public  static  List<String> getAllSupSzflId( List<String> fzyxmConditionId){
        List<String> allSupSzflid =  new ArrayList<>();
        RecordSet rs = new RecordSet();
        String sql = " select szfl from "+UF_FZYCZBKSRXM+" where id in ("+Joiner.on(",").join((Iterable<?>) fzyxmConditionId)+")";
        rs.executeQuery(sql);
        while (rs.next()){
            allSupSzflid.add(rs.getString("szfl"));
        }
        return allSupSzflid;
    }
}
